﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Estimate.Common.Repository
{
    public class HomeReportRepository : BaseRepository
    {
        public HomeReportRepository(string connectionString) : base(connectionString, "HomeReport")
        { }

        public override string GetCreateTableSql()
        {
            //string sqlcreate = String.Format("CREATE TABLE [{0}] (id bigint identity(1,1), startdate date,enddate date,type varchar(10),realtype varchar(8), scorestring text) \n", _tableName);
            //sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (startdate,enddate,type,realtype) \n", _tableName, _tableName);
            //sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            //return sqlcreate;
            return "";
        }

        /// <summary>
        /// 查看首页数据是否存在
        /// </summary>
        /// <param name="key">记录标识</param>
        /// <param name="kind">记录类型</param>
        public bool Exist(DateTime reportdate, string kind)
        {
            string sql = String.Format("SELECT id FROM [{0}] WHERE reportdate=@reportdate and  kind=@kind", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@reportdate", reportdate);
            param[1] = new SqlParameter("@kind", kind);
            return DBHelper.Exists(_connectionString, sql, param);
        }

        /// <summary>
        /// 获取首页数据
        /// </summary>
        /// <param name="key">记录标识</param>
        /// <param name="kind">记录类型</param>
        public string Get( string kind)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE kind=@kind", _tableName);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@kind", kind);
            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);
            if (reader.Read())
            {
                string scorestring = reader["value"].ToString();
                connection.Close();
                return scorestring;
            }
            return "";
        }

        public void Delete(DateTime  begin,DateTime end)
        {
            string sql = String.Format("delete  FROM [{0}] WHERE reportdate>=@begin and reportdate<=@end", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@begin", begin);
            param[1] = new SqlParameter("@end", end);
        
           DBHelper.ExecuteSql(_connectionString, sql, param);
           
        }

        /// <summary>
        /// 将计算好的记录插入到HomeReport表中
        /// </summary>
        /// <param name="key">记录标识</param>
        /// <param name="reportdate">报告时间</param>
        /// <param name="kind">记录类型</param>
        /// <param name="value">成绩记录</param>
        public void Insert(DateTime reportdate, string kind,string value)
        {
            string sql = String.Format("INSERT INTO [{0}] (reportdate,kind,value) values(@reportdate ,@kind,@value)", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@reportdate", reportdate);
            param[1] = new SqlParameter("@kind", kind);
            param[2] = new SqlParameter("@value", value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

    }
}
